using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
	public class TblTransPemBarangDTLEntity
	{
		private Accessor accessor;
		private string connString;

		public TblTransPemBarangDTLEntity (string _connString)
		{
			 this.connString = _connString;
		}

		public int Insert(TblTransPemBarangDTL tbltranspembarangdtl)
		{
			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPemBarangDTL_Insert";
			 accessor.CommandType = CommandType.StoredProcedure;
			 //accessor.Add("@IdTransDTL" ,tbltranspembarangdtl.IdTransDTL );
			 accessor.Add("@IdTransHDR" ,tbltranspembarangdtl.IdTransHDR );
			 accessor.Add("@TglBeli" ,tbltranspembarangdtl.TglBeli );
			 accessor.Add("@Jumlah" ,tbltranspembarangdtl.Jumlah );
			 accessor.Add("@Harga1" ,tbltranspembarangdtl.Harga1 );
			 accessor.Add("@Harga2" ,tbltranspembarangdtl.Harga2 );
			 accessor.Add("@Harga3" ,tbltranspembarangdtl.Harga3 );
			 accessor.Add("@TotalHarga" ,tbltranspembarangdtl.TotalHarga );
			 accessor.Add("@CreatedDate" ,tbltranspembarangdtl.Created );
			 accessor.Add("@CreatedBy" ,tbltranspembarangdtl.CreatedBy );
			 accessor.Add("@ModifiedDate" ,tbltranspembarangdtl.Modified );
			 accessor.Add("@ModifiedBy" ,tbltranspembarangdtl.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Update(TblTransPemBarangDTL tbltranspembarangdtl)
		{
			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPemBarangDTL_Update";
			 accessor.CommandType = CommandType.StoredProcedure;
			 accessor.Add(@"IdTransDTL" ,tbltranspembarangdtl.IdTransDTL );
			 accessor.Add(@"IdTransHDR" ,tbltranspembarangdtl.IdTransHDR );
			 accessor.Add(@"TglBeli" ,tbltranspembarangdtl.TglBeli );
			 accessor.Add(@"Jumlah" ,tbltranspembarangdtl.Jumlah );
			 accessor.Add(@"Harga1" ,tbltranspembarangdtl.Harga1 );
			 accessor.Add(@"Harga2" ,tbltranspembarangdtl.Harga2 );
			 accessor.Add(@"Harga3" ,tbltranspembarangdtl.Harga3 );
			 accessor.Add(@"TotalHarga" ,tbltranspembarangdtl.TotalHarga );
			 //accessor.Add(@"CreatedDate" ,tbltranspembarangdtl.Created );
			 //accessor.Add(@"CreatedBy" ,tbltranspembarangdtl.CreatedBy );
			 accessor.Add(@"ModifiedDate" ,tbltranspembarangdtl.Modified );
			 accessor.Add(@"ModifiedBy" ,tbltranspembarangdtl.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

        public int UpdateJumlah(TblTransPemBarangDTL tbltranspembarangdtl)
        {
            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = "TblTransPemBarangDTL_UpdateJumlah";
            accessor.CommandType = CommandType.StoredProcedure;
            accessor.Add(@"IdTransDTL", tbltranspembarangdtl.IdTransDTL);
            //accessor.Add(@"IdTransHDR", tbltranspembarangdtl.IdTransHDR);
            //accessor.Add(@"TglBeli", tbltranspembarangdtl.TglBeli);
            accessor.Add(@"Jumlah", tbltranspembarangdtl.Jumlah);
            //accessor.Add(@"Harga1", tbltranspembarangdtl.Harga1);
            //accessor.Add(@"Harga2", tbltranspembarangdtl.Harga2);
            //accessor.Add(@"Harga3", tbltranspembarangdtl.Harga3);
            accessor.Add(@"TotalHarga", tbltranspembarangdtl.TotalHarga);
            //accessor.Add(@"CreatedDate" ,tbltranspembarangdtl.Created );
            //accessor.Add(@"CreatedBy" ,tbltranspembarangdtl.CreatedBy );
            accessor.Add(@"ModifiedDate", tbltranspembarangdtl.Modified);
            accessor.Add(@"ModifiedBy", tbltranspembarangdtl.ModifiedBy);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

		public int Delete(TblTransPemBarangDTL tbltranspembarangdtl)
		{
			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPemBarangDTL_Delete";
			 accessor.CommandType = CommandType.StoredProcedure;
			 accessor.Add(@"IdTransDTL" ,tbltranspembarangdtl.IdTransDTL);

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

        public int DeleteByIdHDR(TblTransPemBarangDTL tbltranspembarangdtl)
        {
            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = "TblTransPemBarangDTL_DeleteByIdHDR";
            accessor.CommandType = CommandType.StoredProcedure;
            accessor.Add(@"IdTransHDR", tbltranspembarangdtl.IdTransHDR);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

		public TblTransPemBarangDTL GetDataByID(TblTransPemBarangDTL tbltranspembarangdtl)
		{
			 TblTransPemBarangDTL result = new TblTransPemBarangDTL();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPemBarangDTL_SelectByID";
			 accessor.CommandType = CommandType.StoredProcedure;
			 accessor.Add(@"IdTransDTL" ,tbltranspembarangdtl.IdTransDTL);

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 result.IdTransDTL = rdr.GetInt32(0);
						 result.IdTransHDR = rdr.GetInt32(1);
						 result.TglBeli = rdr.GetDateTime(2);
						 result.Jumlah = rdr.GetInt32(3);
						 result.Harga1 = rdr.GetDecimal(4);
						 result.Harga2 = rdr.GetDecimal(5);
						 result.Harga3 = rdr.GetDecimal(6);
						 result.TotalHarga = rdr.GetDecimal(7);
						 result.Created = rdr.GetDateTime(8);
						 result.CreatedBy = rdr.GetString(9);
						 result.Modified = rdr.GetDateTime(10);
						 result.ModifiedBy = rdr.GetString(11);
					  }
			}
			 return result;
		}

        //public List<TblTransPemBarangDTL> GetDataByIDTransHDR(TblTransPemBarangDTL tbltranspembarangdtl)
        //{
        //    List<TblTransPemBarangDTL> result = new List<TblTransPemBarangDTL>();
        //    accessor = new Accessor(true, connString);
        //    accessor.CommandText = "TblTransPemBarangDTL_SelectByIDTransHDR";
        //    accessor.CommandType = CommandType.StoredProcedure;
        //    accessor.Add(@"IdTransHDR", tbltranspembarangdtl.IdTransHDR);

        //    SqlDataReader rdr = Excutor.GetDataReader(accessor);
        //    if (rdr.HasRows)
        //    {
        //        while (rdr.Read())
        //        {
        //            TblTransPemBarangDTL obj = new TblTransPemBarangDTL();
        //            obj.IdTransDTL = rdr.GetInt32(0);
        //            obj.IdTransHDR = rdr.GetInt32(1);
        //            obj.TglBeli = rdr.GetDateTime(2);
        //            obj.Jumlah = rdr.GetInt32(3);
        //            obj.Harga1 = rdr.GetDecimal(4);
        //            obj.Harga2 = rdr.GetDecimal(5);
        //            obj.Harga3 = rdr.GetDecimal(6);
        //            obj.TotalHarga = rdr.GetDecimal(7);
        //            obj.Created = rdr.GetDateTime(8);
        //            obj.CreatedBy = rdr.GetString(9);
        //            obj.Modified = rdr.GetDateTime(10);
        //            obj.ModifiedBy = rdr.GetString(11);

        //            result.Add(obj);
        //        }
        //    }
        //    return result;
        //}

        public DataSet GetDataByIDTransHDR(string IdTransHDR)
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("Select 		IdTransDTL	AS	'IdTransDTL',");
		    s.AppendLine("IdTransHDR	AS	'IdTransHDR',");
		    s.AppendLine("CONVERT(VARCHAR,TglBeli,113)	AS	'TglBeliBarang',");
		    s.AppendLine("Jumlah	AS	'Jumlah',");
		    s.AppendLine("Harga1	AS	'Harga1',");
		    s.AppendLine("Harga2	AS	'Harga2',");
		    s.AppendLine("Harga3	AS	'Harga3',");
		    s.AppendLine("TotalHarga	AS	'TotalHarga'");
            s.AppendLine("From	TblTransPemBarangDTL");
            s.AppendLine("WHERE IdTransHDR ="+IdTransHDR);
            s.AppendLine("AND JUMLAH <> 0 ");
            s.AppendLine("ORDER BY IdTransDTL DESC");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            //accessor.Add(@"IdTransHDR", tbltranspembarangdtl.IdTransHDR);

            result = Excutor.GetDataSet(accessor);
            return result;
        }


		public List<TblTransPemBarangDTL> GetDataAll()
		{
			 List<TblTransPemBarangDTL> result = new List<TblTransPemBarangDTL>();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPemBarangDTL_SelectAll";
			 accessor.CommandType = CommandType.StoredProcedure;

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 TblTransPemBarangDTL obj = new TblTransPemBarangDTL();
						 obj.IdTransDTL = rdr.GetInt32(0);
						 obj.IdTransHDR = rdr.GetInt32(1);
						 obj.TglBeli = rdr.GetDateTime(2);
						 obj.Jumlah = rdr.GetInt32(3);
						 obj.Harga1 = rdr.GetDecimal(4);
						 obj.Harga2 = rdr.GetDecimal(5);
						 obj.Harga3 = rdr.GetDecimal(6);
						 obj.TotalHarga = rdr.GetDecimal(7);
						 obj.Created = rdr.GetDateTime(8);
						 obj.CreatedBy = rdr.GetString(9);
						 obj.Modified = rdr.GetDateTime(10);
						 obj.ModifiedBy = rdr.GetString(11);
						 result.Add(obj);

                         

					  }
			}
			 return result;
		}

        public SqlCommand GetCommandReport()
        {
            SqlCommand comm = new SqlCommand();
             
             StringBuilder s = new StringBuilder();
             s.AppendLine("SELECT C.NamaBarang AS Barang");
             s.AppendLine(",B.TglBeli");
             s.AppendLine(",D.Satuan");
             s.AppendLine(",B.Harga1");
             s.AppendLine(",B.Harga2");
             s.AppendLine(",B.Harga3");
             s.AppendLine(",B.Jumlah");
             s.AppendLine("FROM TblTransPembBarangHDR AS A");
             s.AppendLine("INNER JOIN TblTransPemBarangDTL AS B ON A.IdTrans = B.IdTransHDR");
             s.AppendLine("INNER JOIN TblBarang AS C ON A.IdBarang = C.Id");
             s.AppendLine("INNER JOIN TblSatuan AS D ON D.Id = C.IdSatuan");
             s.AppendLine("ORDER BY B.TglBeli DESC");
            
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }

        public SqlCommand GetCommandReportDATE(DateTime tglMulai,DateTime tglAkhir)
        {
            SqlCommand comm = new SqlCommand();

            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT C.NamaBarang AS Barang");
            s.AppendLine(",B.TglBeli");
            s.AppendLine(",D.Satuan");
            s.AppendLine(",B.Harga1");
            s.AppendLine(",B.Harga2");
            s.AppendLine(",B.Harga3");
            s.AppendLine(",B.Jumlah");
            s.AppendLine("FROM TblTransPembBarangHDR AS A");
            s.AppendLine("INNER JOIN TblTransPemBarangDTL AS B ON A.IdTrans = B.IdTransHDR");
            s.AppendLine("INNER JOIN TblBarang AS C ON A.IdBarang = C.Id");
            s.AppendLine("INNER JOIN TblSatuan AS D ON D.Id = C.IdSatuan");
            s.AppendLine("WHERE convert(varchar, b.tglbeli, 103)>='"+tglMulai.ToString("dd/MM/yyyy")+"'");
            s.AppendLine("AND convert(varchar, b.tglbeli, 103)<='" + tglAkhir.ToString("dd/MM/yyyy") + "'");
            s.AppendLine("ORDER BY B.TglBeli DESC");
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }



        public DataSet GetFindDataAll()
        {
            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = "TblTransPemBarangDTL_SelectFindAll";
            accessor.CommandType = CommandType.StoredProcedure;

            result = Excutor.GetDataSet(accessor);
           
            return result;
        }

        public DataSet GetFindDataAll(string parameter,string value)
        {
            if (parameter.Trim().Contains("NamaBarang"))
                parameter = "WHERE C.NamaBarang LIKE '%" + value + "%'";

            else if (parameter.Trim().Contains("KelompokBarang"))
                parameter = "WHERE E.Kelompok LIKE '%" + value + "%'";

            else if (parameter.Trim().Contains("SatuanBarang"))
                parameter = "WHERE D.SATUAN LIKE '%" + value + "%'";


            else if (parameter.Trim().Contains("TglBeli"))
            {
                string[] param = value.Split('|');
                parameter = "WHERE CONVERT(varchar,B.TglBeli,103)>='" + param[0].ToString() + "'  AND  CONVERT(varchar,B.TglBeli,103)<= '" + param[1].ToString() + "'";
            }

            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT"); 
            s.AppendLine(" C.NamaBarang");
            s.AppendLine(",convert(varchar, b.tglbeli, 103) AS TglBeli");
            s.AppendLine(",D.Satuan");
            s.AppendLine(",E.Kelompok");
            s.AppendLine(",B.Harga1");
            s.AppendLine(",B.Harga2");
            s.AppendLine(",B.Harga3");
            //s.AppendLine(",B.IdTransDTL AS ID");
            //s.AppendLine(",A.IdTransHDR AS ID");
            s.AppendLine(",A.IdBarang AS IDBarang");
            s.AppendLine(",convert(varchar,B.Jumlah) +'('+D.Satuan+')' AS Jumlah");
            s.AppendLine("FROM TblTransPembBarangHDR AS A INNER JOIN TblTransPemBarangDTL AS B");
            s.AppendLine("ON A.IdTrans = B.IdTransHDR"); 
            s.AppendLine("INNER JOIN TblBarang AS C ON A.IdBarang = C.Id INNER JOIN");
            s.AppendLine("TblSatuan AS D ON D.Id = C.IdSatuan INNER JOIN TblKelompok AS E");
            s.AppendLine("ON E.Id = C.IdKelompok");
            s.AppendLine(parameter);
            s.AppendLine("AND B.Jumlah <> 0");
            s.AppendLine("ORDER BY B.TglBeli DESC");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            result = Excutor.GetDataSet(accessor);

            return result;
        }



	}
}
